/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Department;
import dto.Employee;
import dto.EmployeeDiary;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.DateFormat;
import utils.SQLServerDB;

/**
 *
 * @author Canh
 */
public class EmployeeDiaryDAO {

    final String EMPLOYEEDIARY_READALL = "SELECT * FROM EMPLOYEEDIARYS";
    final String EMPLOYEEDIARY_READ_BY_EMPLOYEE = "SELECT * FROM EMPLOYEEDIARYS WHERE [EmployeeNumber] = ?";
    final String EMPLOYEEDIARY_READ_LAST_BY_EMPLOYEE = "SELECT  top(1) * FROM EMPLOYEEDIARYS WHERE [EmployeeNumber] = ? order by CreateDate";
    final String EMPLOYEEDIARY_READ_BY_DATE = "SELECT * FROM EMPLOYEEDIARYS WHERE [CreateDate] >= ? AND [CreateDate] <= ?";
    final String EMPLOYEEDIARY_READBYID = "SELECT * FROM EMPLOYEEDIARYS WHERE ID = ?";
    final String EMPLOYEEDIARY_CREATE = "{call Diary_Create(?, ?, ?, ?, ?)}";
    final String EMPLOYEEDIARY_DELETE = "DELETE EMPLOYEEDIARYS WHERE ID = ?";
    final String EMPLOYEEDIARY_UPDATE = "{call Diary_Create(?, ?, ?, ?)}";
    final String EMPLOYEEDIARY_APPROVAL = "UPDATE EMPLOYEEDIARYS Set [Manager] = ?,[TransferRelievingDate] = ? [TransferJoiningDate] = ?, [Information] =? WHERE [ID] =?";
    final String EMPLOYEEDIARY_UNAPPROVAL = "UPDATE EMPLOYEEDIARYS Set [Manager] = ?, [Information] =? WHERE [ID] =?";

    public boolean create(EmployeeDiary emp) {
        try {
            SQLServerDB db = new SQLServerDB();
            CallableStatement pr = db.getConn().prepareCall(EMPLOYEEDIARY_CREATE);
            pr.setInt(1, emp.getEmployeeNumber());
            pr.setInt(2, emp.getTransferProject());
            pr.setInt(3, emp.getTransferDepartment());
            pr.setString(4, emp.getReason());
            pr.setDate(5, DateFormat.toSqlDate(emp.getCreateDate()));
            pr.execute();
        } catch (SQLException ex) {
            return false;
        }
        return true;
    }

    public ArrayList<EmployeeDiary> readAll() {

        ArrayList<EmployeeDiary> diaries = new ArrayList<EmployeeDiary>();
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(EMPLOYEEDIARY_READALL);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                EmployeeDiary emp = readFromData(rs);
                diaries.add(emp);
            }
        } catch (SQLException ex) {

        }
        return diaries;
    }

    public ArrayList<EmployeeDiary> readByEmployee(Employee e) {
        ArrayList<EmployeeDiary> diaries = new ArrayList<EmployeeDiary>();
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(EMPLOYEEDIARY_READ_BY_EMPLOYEE);
            pr.setInt(1, e.getEmpNum());
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                EmployeeDiary emp = readFromData(rs);
                diaries.add(emp);
            }
        } catch (SQLException ex) {

        }
        return diaries;
    }

    public EmployeeDiary lastTransfer(Employee e) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(EMPLOYEEDIARY_READ_LAST_BY_EMPLOYEE);
            pr.setInt(1, e.getEmpNum());
            ResultSet rs = pr.executeQuery();
            if (rs.next()) {
                EmployeeDiary emp = readFromData(rs);
                return emp;
            }
        } catch (SQLException ex) {

        }
        return null;
    }

    public ArrayList<EmployeeDiary> readUnApproval(Date startDate, Date endDate) {
        ArrayList<EmployeeDiary> diaries = new ArrayList<EmployeeDiary>();
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(EMPLOYEEDIARY_READ_BY_EMPLOYEE);
            pr.setDate(1, new java.sql.Date(startDate.getTime()));
            pr.setDate(2, new java.sql.Date(endDate.getTime()));
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                diaries.add(readFromData(rs));
            }
        } catch (SQLException ex) {

        }
        return diaries;
    }

    static EmployeeDiary readFromData(ResultSet rs) {
        try {
            EmployeeDiary emp = new EmployeeDiary();
            emp.setId(rs.getInt("ID"));
            emp.setApproval(rs.getInt("Approval"));
            emp.setCreateDate(DateFormat.toJavaDate(rs.getDate("CreateDate")));
            emp.setCurrentDepartment(rs.getInt("CurrentDepartment"));
            emp.setCurrentProject(rs.getInt("CurrentProject"));
            emp.setEmployeeNumber(rs.getInt("EmployeeNumber"));
            emp.setInformation(rs.getString("Information"));
            emp.setManager(rs.getInt("Manager"));
            emp.setReason(rs.getString("Reason"));
            emp.setTransferDepartment(rs.getInt("TransferDepartment"));
            emp.setTransferJoiningDate(DateFormat.toJavaDate(rs.getDate("TransferJoiningDate")));
            emp.setTransferProject(rs.getInt("TransferProject"));
            emp.setTransferRelievingDate(DateFormat.toJavaDate(rs.getDate("TransferRelievingDate")));
            return emp;
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDiaryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean update(EmployeeDiary emp) {

        try {
            SQLServerDB db = new SQLServerDB();
            CallableStatement pr = db.getConn().prepareCall(EMPLOYEEDIARY_CREATE);
            pr.setInt(1, emp.getId());
            pr.setInt(2, emp.getTransferProject());
            pr.setInt(3, emp.getTransferDepartment());
            pr.setString(4, emp.getReason());
            pr.execute();
        } catch (SQLException ex) {
            return false;
        }
        return true;
    }

    public boolean approvalTranfer(EmployeeDiary emp, Employee manager) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(EMPLOYEEDIARY_APPROVAL);
            pr.setInt(1, manager.getEmpNum());
            pr.setDate(2, DateFormat.toSqlDate(emp.getTransferRelievingDate()));
            pr.setDate(3, DateFormat.toSqlDate(emp.getTransferJoiningDate()));
            pr.setString(4, emp.getInformation());
            pr.setInt(5, emp.getId());
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;

    }

    public boolean unapprovalTranfer(EmployeeDiary emp, Employee manager) {
        try {
            SQLServerDB db = new SQLServerDB();
            PreparedStatement pr = db.getConn().prepareStatement(EMPLOYEEDIARY_UNAPPROVAL);
            pr.setInt(1, manager.getEmpNum());
            pr.setString(2, emp.getInformation());
            pr.setInt(3, emp.getId());
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;

    }

    public boolean delete(int id) {
        try {

            SQLServerDB db = new SQLServerDB();
            CallableStatement pr = db.getConn().prepareCall(EMPLOYEEDIARY_DELETE);
            pr.setInt(1, id);
            if (pr.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }

}
